import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

def debug_data_preprocessing():
    """调试数据预处理"""
    print("=== 调试数据预处理 ===")
    
    # 1. 加载训练数据
    print("1. 加载训练数据...")
    df_train = pd.read_excel('1.xlsx')
    print(f"训练数据形状: {df_train.shape}")
    print(f"列名: {df_train.columns.tolist()}")
    print(f"数据类型: {df_train.dtypes.to_dict()}")
    print(f"空值统计: {df_train.isnull().sum().to_dict()}")
    print(f"前几行数据:\n{df_train.head()}")
    
    # 2. 检查日期列
    print("\n2. 检查日期列...")
    date_col = 'finish_time'
    if date_col in df_train.columns:
        print(f"日期列 '{date_col}' 存在")
        print(f"日期范围: {df_train[date_col].min()} 到 {df_train[date_col].max()}")
        print(f"日期数据类型: {df_train[date_col].dtype}")
        
        # 转换日期格式
        try:
            df_train[date_col] = pd.to_datetime(df_train[date_col])
            print("日期转换成功")
        except Exception as e:
            print(f"日期转换失败: {e}")
    else:
        print(f"日期列 '{date_col}' 不存在")
        print("可用列名:", df_train.columns.tolist())
    
    # 3. 检查销售数据
    print("\n3. 检查销售数据...")
    value_col = 'value'
    if value_col in df_train.columns:
        print(f"销售列 '{value_col}' 存在")
        print(f"销售数据统计:\n{df_train[value_col].describe()}")
        print(f"非零销售数量: {(df_train[value_col] > 0).sum()}")
        print(f"零销售数量: {(df_train[value_col] == 0).sum()}")
        print(f"空值数量: {df_train[value_col].isnull().sum()}")
    else:
        print(f"销售列 '{value_col}' 不存在")
    
    # 4. 加载预测数据
    print("\n4. 加载预测数据...")
    df_pred = pd.read_excel('2.xlsx')
    print(f"预测数据形状: {df_pred.shape}")
    print(f"列名: {df_pred.columns.tolist()}")
    print(f"数据类型: {df_pred.dtypes.to_dict()}")
    print(f"前几行数据:\n{df_pred.head()}")
    
    return df_train, df_pred

def create_simple_predictions(df_train, df_pred):
    """创建简单预测"""
    print("\n=== 创建简单预测 ===")
    
    # 获取历史非零销售的平均值
    if 'value' in df_train.columns:
        non_zero_sales = df_train['value'][df_train['value'] > 0]
        if len(non_zero_sales) > 0:
            avg_sales = non_zero_sales.mean()
            print(f"历史非零销售平均值: {avg_sales:.2f}")
        else:
            avg_sales = 10.0  # 默认值
            print("没有非零销售数据，使用默认值: 10.0")
    else:
        avg_sales = 10.0
        print("没有销售数据，使用默认值: 10.0")
    
    # 创建预测结果
    n_predictions = len(df_pred)
    
    # 策略1: 基于历史均值，添加一些随机性
    predictions_mean = np.full(n_predictions, avg_sales)
    
    # 策略2: 考虑工作日/周末模式
    if 'finish_time' in df_pred.columns:
        df_pred_copy = df_pred.copy()
        df_pred_copy['finish_time'] = pd.to_datetime(df_pred_copy['finish_time'])
        df_pred_copy['weekday'] = df_pred_copy['finish_time'].dt.weekday
        
        # 工作日销售较高，周末较低
        weekday_factor = np.where(df_pred_copy['weekday'] < 5, 1.2, 0.8)
        predictions_pattern = avg_sales * weekday_factor
    else:
        predictions_pattern = np.full(n_predictions, avg_sales)
    
    # 策略3: 添加随机波动
    np.random.seed(42)  # 固定随机种子
    random_factor = np.random.normal(1.0, 0.3, n_predictions)
    predictions_final = predictions_pattern * random_factor
    
    # 确保非负
    predictions_final = np.maximum(predictions_final, 0)
    
    # 四舍五入到整数
    predictions_final = np.round(predictions_final)
    
    print(f"预测数量: {len(predictions_final)}")
    print(f"预测统计:\n{pd.Series(predictions_final).describe()}")
    
    return predictions_final

def save_predictions(df_pred, predictions, output_file='3.xlsx'):
    """保存预测结果"""
    print(f"\n=== 保存预测结果到 {output_file} ===")
    
    # 创建结果DataFrame
    results_df = pd.DataFrame()
    
    # 复制日期列
    if 'finish_time' in df_pred.columns:
        results_df['finish_time'] = df_pred['finish_time']
    else:
        # 如果没有日期列，创建默认日期
        start_date = datetime.now().date()
        dates = [start_date + timedelta(days=i) for i in range(len(predictions))]
        results_df['finish_time'] = dates
    
    # 添加预测值
    results_df['predicted_sales'] = predictions
    
    # 保存到Excel
    results_df.to_excel(output_file, index=False)
    
    print(f"结果已保存到: {output_file}")
    print(f"预测记录数: {len(results_df)}")
    print(f"结果预览:\n{results_df.head()}")
    
    return results_df

def main():
    """主函数"""
    print("=== 手机配件销售预测系统 (调试版) ===")
    print(f"开始时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    
    try:
        # 1. 调试数据预处理
        df_train, df_pred = debug_data_preprocessing()
        
        # 2. 创建简单预测
        predictions = create_simple_predictions(df_train, df_pred)
        
        # 3. 保存结果
        results_df = save_predictions(df_pred, predictions)
        
        print(f"\n=== 流程完成 ===")
        print(f"结束时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print("✓ 销售预测系统运行成功！")
        
        return results_df
        
    except Exception as e:
        print(f"\n✗ 系统运行失败: {e}")
        import traceback
        traceback.print_exc()
        return None

if __name__ == "__main__":
    results = main()